# 获取数据表字段
import sys

from base.Method import BM
from util.DataQuery.hzflowOperation import hzflowOperation
from util.g import g
from settings import online_config, site_type, web_type


class DbTools():
    dict_cursor = g.dict_cursor

    POOL = g.hitech_pool


    @classmethod
    def get_conn(cls, db_config={}, database=''):
        a = None
        while True:
            try:
                a = cls.POOL.connection()
            except Exception as err:
                pass
            else:
                break
        return a

    """
    获取附件
    """
    @staticmethod
    def get_pdf_record(co_id, year, rule_id):
        sql = """select * from ent_res_pdf where co_id={} and year={} and web_type='{}' and rule_id={}""".format(
            co_id, year, web_type, rule_id
        )
        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(cursor=DbTools.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()

        return data

    @staticmethod
    def get_hitech_record_by_sql(sql="", model="one"):
        if not sql:
            return {}
        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(cursor=DbTools.dict_cursor)
        data = None
        if model != "one":
            data = []
        try:
            cursor.execute(sql)
            if model == "one":
                data = cursor.fetchone()
            else:
                data = cursor.fetchall()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()

        return data

    @staticmethod
    def get_co_id_by_creditcode(creditcode):
        sql = "select co_id from ent_info where creditcode='{}' for update".format(creditcode)

        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(cursor=DbTools.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()

        return data.get("co_id") if data else data

    @staticmethod
    def get_entyearstatus_by_co_id(co_id, year):
        sql = "select * from ent_yearstatus where co_id={co_id} and year={year} for update".format(co_id=co_id,
                                                                                                   year=year)
        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(DbTools.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()
        return data

    @staticmethod
    def get_entyearstatus_by_status(status=None, year=None):
        sql = """select co_id, status from ent_yearstatus 
        where status={status} and year={year} and ((mw_audit={mw_audit} and (area != 320583 
        or city != 320300 or area != 320481 or city != 321000)) 
        or (mw_audit in (0, 1, 2) and (area = 320583 or city =320300 or area=320481 or city=321000))) for update""".format(
            status=status,
            year=year, mw_audit=2)
        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(DbTools.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()
        return data

    @staticmethod
    def get_entresstatus_by_co_id(co_id:int, year:int):
        sql = """select *
        from ent_res_status where co_id={co_id} and year={year} and res_code='{site_type}' for update""".format(
            co_id=co_id, year=year, site_type=site_type)

        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(DbTools.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()
        return data

    @staticmethod
    def get_ipn_data_by_co_id(co_id:int, year:int):
        sql = """select *
        from ent_res_ipn where co_id={co_id} and year={year} for update""".format(
            co_id=co_id, year=year, site_type=site_type)

        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(DbTools.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()
        return data

    @staticmethod
    def get_entresstatus_by_co_ids(co_ids:str, year:int):
        # y.stat_id, y.res_code, y.co_id, y.year, y.file_stat, y.fb_status, y.fb_time, y.ul_status, y.entname,
        # y.c_syncstatus,
        # y.c_flowstatus, y.c_username, y.c_userpwd, y.phone, y.c_start_sms, y.c_end_sms, y.c_idstatus
        sql = """select 
        r.*  from 
        ent_res_status r join ent_yearstatus y on r.co_id=y.co_id and r.year=y.year where r.co_id in ({}) 
        and r.year={} and r.res_code='{}' and (r.ul_status < 2 and r.ul_status != -1) and r.c_syncstatus=0
        order by y.update_time for update""".format(co_ids, year, site_type)
        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(DbTools.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
        except Exception as err:
            msg = "{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err)
            if not g.logger:
                print(msg)
            else:
                g.logger.error(msg)
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()
        return data

    @staticmethod
    def update_entrestatus_fields(map:dict, co_id:int=None, year:int=None, table_name="ent_res_status"):
        sql = """update {} set {} where co_id={} and year={} and res_code='{}'""".format(
            table_name, ", ".join("{}='{}'".format(k, v) for k, v in map.items()), co_id, year, site_type
        )
        g.logger.info("sql: {}".format(sql))
        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(DbTools.dict_cursor)
        count = 0
        try:
            count = cursor.execute(sql)
        except Exception as err:
            client_obj.rollback()
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()
        if map.get("c_syncstatus") in (1, 3, 4):
            # 将 ent_flow_data 中的数据放入 ent_flow_datalog 中
            hzflowOperation.tran_to_data(co_id=co_id, year=year, status=map.get("c_syncstatus"))
        if map.get("ul_status") in (-1, ):
            hzflowOperation.tran_to_data(co_id=co_id, year=year, status=map.get("ul_status"))
        return count

    @staticmethod
    def update_entresstatus_field(field_name="", field_value="", co_id=None, year=None):
        "update ent_res_status set ul_status=2 where co_id={} and year={} and c_syncstatus=1 and p_syncstatus=1"
        if not all([co_id, year]):
            raise Exception("need co_id and year")
        sql = "update ent_res_status set {} = {} where co_id={} and year={} and res_code='{}'".format(
            field_name, f"'{field_value}'" if type( field_value) == str else f"{field_value}", co_id, year, site_type
        )
        g.logger.info("sql: {}".format(sql))
        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(DbTools.dict_cursor)
        count = 0
        try:
            count = cursor.execute(sql)
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()
        return count

    @staticmethod
    def update_ent_resstatus_ul_status(co_id=None, year=None):
        sql = "update ent_res_status set ul_status=2 where co_id={} and year={} and c_syncstatus=1 and p_syncstatus=1".format(
            co_id, year)
        client_obj = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client_obj.cursor(DbTools.dict_cursor)
        count = 0
        try:
            count = cursor.execute(sql)
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client_obj.commit()
        finally:
            cursor.close()
            client_obj.close()
        return count

    @classmethod
    def get_doc_path_by_info(cls, co_id=None, biz_code="", description=""):
        sql = """select * from ent_files where co_id={} and biz_code='{}' and biz_type='resStatus' and doc_type='pdf' and description='{}'""".format(
            co_id, biz_code, description)
        client = DbTools.get_conn(db_config=online_config, database="hitech")
        cursor = client.cursor(DbTools.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client.commit()
        finally:
            cursor.close()
            client.close()
        return data

    @classmethod
    def get_area_code_by_co_id(cls, co_id=None, year=None):
        sql = """select province from ent_info where co_id={} and year={} for update""".format(co_id, year)
        client = cls.get_conn(db_config=online_config, database="hitech")
        cursor = client.cursor(cls.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        finally:
            cursor.close()
            client.close()
        return data

    @classmethod
    def get_hitech_name_by_ids(cls, ids=""):
        sql = """select id, name from sys_hitechfield where id in ({})""".format(ids)
        client = cls.get_conn(db_config=online_config, database="hitech")
        cursor = client.cursor(cursor=cls.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client.commit()
        finally:
            cursor.close()
            client.close()
        return data

    # 获取企业附件信息 查看附件是否上传完成
    @classmethod
    def get_ent_files_completed(cls, co_id, year, description=0):
        sql = """select * from ent_files where co_id={} and year={} and biz_type='{}' and description={}""".format(
            co_id, year, "resStatus", description
        )
        client = cls.get_conn(db_config=online_config, database="hitech")
        cursor = client.cursor(cursor=cls.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client.commit()
        finally:
            cursor.close()
            client.close()
        return data and data.get("is_split") == 1

    # 获取企业附件信息 查看附件压缩完成
    @classmethod
    def get_flow_compress(cls, co_id, year, ):
        sql = """select * from hzflow.ent_flow_data where co_id={} and year={} and res_code='{}'""".format(
            co_id, year, "GR"
        )
        client = cls.get_conn(db_config=online_config, database="hitech")
        cursor = client.cursor(cursor=cls.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchone()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client.commit()
        finally:
            cursor.close()
            client.close()
        return data and data.get("is_compress") == 1

    # 根据 co_id 获取 指定标记的记录和 标记为空的记录
    @classmethod
    def get_flow_by_co_ids(cls, co_ids:str=[], year:int=0, mark_info:str=""):
        if not co_ids:
            return []
        if not mark_info:
            sql = """
                    select * from hzflow.ent_flow_data where  res_code='{}' and year={} and co_id in ({})
            """.format("GR", year, co_ids)
        else:
            sql = """
            select * from hzflow.ent_flow_data where ((ISNULL(mark_info) = 1 or mark_info="") {}) 
            and res_code='{}' and year={} and co_id in ({})
            """.format(" or mark_info = '{}'".format(mark_info),
                       "GR", year, co_ids)
        client = cls.get_conn(db_config=online_config, database="hitech")
        cursor = client.cursor(cursor=cls.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client.commit()
        finally:
            cursor.close()
            client.close()
        return data

    # 获取所有测试企业 不上传材料
    @classmethod
    def get_all_test(cls):
        sql = """
        select * from hzflow.ent_res_test """
        client = cls.get_conn(db_config=online_config, database="hitech")
        cursor = client.cursor(cursor=cls.dict_cursor)
        data = None
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client.commit()
        finally:
            cursor.close()
            client.close()
        # 直接返回 co_id 列表
        return [] if not data else [i.get("co_id") for i in data]

    # 获取上传中的 ent_res_status 列表
    @classmethod
    def get_uploading_status(cls, year=None):
        sql = """
        select r.* from ent_yearstatus y,ent_res_status r where y.co_id=r.co_id and ul_status<2 
        and y.year=r.year and r.year={}  and r.c_syncstatus=0 and y.status=2  
        and ((mw_audit in (0,1,2) and city != 320400) or (mw_audit = 2 and  city = 320400))
        and r.res_code="GR"  and ul_status != -1 order by y.update_time
        """.format(year)
        client = cls.get_conn(db_config=online_config, database="hitech")
        cursor = client.cursor(cursor=cls.dict_cursor)
        data = []
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
        except Exception as err:
            g.logger.error("{} error sql={} err={}".format(sys._getframe().f_code.co_name, sql, err))
        else:
            client.commit()
        finally:
            cursor.close()
            client.close()
        return data

    @classmethod
    def get_mark_num(cls, year=None):
        # 获取上传中的 ent_res_status 记录
        uploading_status = cls.get_uploading_status(year)
        # 获取 uploading_status 对应的 上传状态记录 并筛选出 本机标记的记录 和没有对应上传状态的记录
        uploading_co_ids = [str(i.get("co_id")) for i in uploading_status]
        mark_info: str = BM.get_host()
        flows = cls.get_flow_by_co_ids(co_ids=",".join(uploading_co_ids),
                                       year=year, mark_info=mark_info)
        mark_list = []
        for record in flows:
            re_co_id = record.get("co_id")
            test_list = DbTools.get_all_test()
            if re_co_id in test_list:
                continue
            if record.get("mark_info") == mark_info:
                mark_list.append(re_co_id)
        g.mark_num = len(mark_list)


    @classmethod
    def get_list(cls, year:int=None, status:int=2, test:bool=False, co_ids:list=[]):
        if test:
            if co_ids:
                return cls.get_entresstatus_by_co_ids(co_ids=",".join([str(i) for i in co_ids]), year=2021)
            # a = cls.get_entresstatus_by_co_id(co_id=307566, year=2021)
            # a = cls.get_entresstatus_by_co_id(co_id=406834, year=2021)
            if co_ids == 0:
                li = "114398,116254,117967,118493,122379,123979,482529,514096,514967,516326,517306,81557,882532,994962"
                a = cls.get_entresstatus_by_co_ids(co_ids=li, year=2021)
                return a
            a = cls.get_entresstatus_by_co_id(co_id=121573, year=2021)
            if not isinstance(a, list):
                return [a]
            else:
                return a

        # 获取上传中的 ent_res_status 记录
        uploading_status = cls.get_uploading_status(year)

        # 获取 uploading_status 对应的 上传状态记录 并筛选出 本机标记的记录 和没有对应上传状态的记录
        uploading_co_ids = [str(i.get("co_id")) for i in uploading_status]
        mark_info:str = BM.get_host()
        flows = cls.get_flow_by_co_ids(co_ids=",".join(uploading_co_ids),
                                       year=year, mark_info=mark_info)

        flow_records = cls.get_flow_by_co_ids(co_ids=",".join(uploading_co_ids), year=year)
        flows_ids = [str(i.get("co_id")) for i in flow_records]
        test_list = DbTools.get_all_test()

        mark_list = []
        no_record_list = []
        for record in flows:
            re_co_id = record.get("co_id")
            if re_co_id in test_list:
                continue
            if record.get("mark_info") == mark_info:
                mark_list.append(re_co_id)
        for cid in uploading_co_ids:
            if int(cid) in test_list:
                continue
            if cid not in flows_ids:
                no_record_list.append(int(cid))

        g.mark_num = len(mark_list)
        if len(mark_list) < g.max_mark:
            mark_list.extend(no_record_list[:g.max_mark - g.mark_num])

        for i, sta in enumerate(uploading_status[:]):
            if not all([sta.get("c_username"), sta.get("c_userpwd")]):
                # TODO 给用户发送短信提示国网 用户名或密码未填写 然后 退出或 continue
                uploading_status.remove(sta)
                continue

        res = []
        for item in uploading_status:
            if item.get("co_id") in mark_list:
                res.append(item)
        return res


if __name__ == '__main__':
    # a = DbTools.get_entresstatus_by_co_id(co_id=307566, year=2020)
    # file_stat = a.get("file_stat")
    # li = {i.split(":")[0]: i.split(":")[1] for i in file_stat.split(",")}
    # print(li)
    """explain update ent_res_status set ul_status=2 where co_id=307566 and year=2020 and c_syncstatus=1 and p_syncstatus=1"""
    # g.year = 2021
    # status_records = DbTools.get_list(year=g.year, status=2, test=False)
    # print(status_records)
    # status_records = DbTools.get_list(year=g.year, status=2)
    # print(status_records)

    # ipn_data = DbTools.get_ipn_data_by_co_id(co_id=30756, year=2021)
    # print(ipn_data)
    # print("中介" if ipn_data else "企业")

    a = [617344,961189,1060054,565225,335638,985212,475618,882760,1081863,515695,116190,861883]
    b = [617344,517301,961189,61800,1010817,550928,807318,114615,315728,118512,1060054,565225,335638,72236,995927,92888,117588,86969,985212,475618,882760,888272,115909,201241,180562,1081863,517215,515695,882484,116190,861883]
    res = []
    for i in b:
        if i not in a:
            res.append(i)
    print(",".join([str(i) for i in res]))


# "扬州博泰冶金设备制造有限公司",
# "扬州星汉玻璃工艺品有限公司",
# "扬州永宏石油科技有限公司",
# "江苏荣业科技有限公司",
# "扬州明伟铝业有限公司",
# "常州旺童半导体科技有限公司",
# "生合生物科技（扬州）有限公司",
# "扬州新大通信息网络技术有限公司",


